﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Linq;
using System.Text.RegularExpressions;
using Dapper;
using Dapper.Contrib.Extensions;

namespace Topro.Application.Impl
{
    using Topro.Application.Interface;
    using Topro.Db;
    using Topro.Entity;
    using Topro.Entity.Enum;
    using Topro.Entity.Query;
    using Topro.Entity.Request.Menu;
    using Topro.Entity.Request.Permission;
    using Topro.Entity.Request.Role;
    using Topro.Entity.Request.User;
    using Topro.Entity.Response.Menu;
    using Topro.Entity.Response.Permission;
    using Topro.Entity.Response.Role;
    using Topro.Entity.Response.User;
    using Topro.Entity.Tables;
    using Topro.Log;
    using Topro.Util;

    /// <summary>
    /// 权限接口实现
    /// </summary>
    public class AuthServiceImpl: AuthService
    {
        #region 注入
        /// <summary>
        /// 数据接口
        /// </summary>
        private readonly IDb _db;

        /// <summary>
        /// loggerService
        /// </summary>
        private readonly LoggerService _loggerService;

        /// <summary>
        /// 
        /// </summary>
        /// <param name="dapperFactory"></param>
        /// <param name="logger"></param>
        public AuthServiceImpl(IDb db, LoggerService loggerService)
        {
            _db = db;
            _loggerService = loggerService;
        }
        #endregion

        #region 用户
        /// <summary>
        /// 获取用户
        /// </summary>
        /// <param name="id">编号</param>
        /// <returns></returns>
        public UserResponse GetUser(int id)
        {
            var userResponse = _db.DbConnection.QueryFirst<UserResponse>("select * from user where Id=@Id", new { Id = id });
            if(userResponse != null)
            {
                return userResponse;
            }

            return null;
        }

        /// <summary>
        /// 获取用户
        /// </summary>
        /// <param name="id">编号</param>
        /// <returns></returns>
        public UserResponse GetUser(int? id)
        {
            var userResponse = new UserResponse();
            if (id.HasValue)
            {
                userResponse = _db.DbConnection.QueryFirst<UserResponse>("select * from user where Id=@Id", new { Id = id });
                //已选择角色;
                var _roles = _db.DbConnection.Query<RoleResponse>("SELECT * FROM role WHERE Id in(SELECT RoleId FROM userrole WHERE UserId = @UserId)", new { UserId = userResponse.Id }).ToList();
                if (_roles != null && _roles.Any())
                {

                    var _selectedRole = new List<RoleResponse>();

                    _roles.ForEach(item => {
                        _selectedRole.Add(new RoleResponse()
                        {
                            Id = item.Id,
                            Name = item.Name
                        });
                    });

                    if (_selectedRole != null && _selectedRole.Any())
                    {
                        userResponse.SelectedRole = _selectedRole;
                    }
                }
            }

            //所有启用角色
            var roles = _db.DbConnection.Query<RoleResponse>("SELECT * FROM role WHERE Status = @Status", new { Status = (int)RoleStatus.启用 }).ToList();
            if (roles != null && roles.Any())
            {
                userResponse.NoSelectedRole = roles;
            }
            return userResponse;
        }

        /// <summary>
        /// 更新状态
        /// </summary>
        /// <returns></returns>
        public JResult UpdateUserStatus(int id, int status)
        {
            var response = new JResult()
            {
                Success = false
            };

            try
            {
                var user = _db.DbConnection.QueryFirst<UserResponse>("select * from user where Id=@Id", new { Id = id });
                if (user == null)
                {
                    throw new Exception("用户记录不存在.");
                }

                var updateSql = "UPDATE user SET Status=@Status WHERE Id=@Id";

                int result = _db.DbConnection.Execute(updateSql,new { Status = status,Id = id });
                if(result<=0)
                {
                    throw new Exception("更新失败");
                }
                response.Success = true;
                response.Message = "操作成功.";
            }
            catch (Exception ex)
            {
                response.Message = ex.Message;
            }

            return response;
        }

        /// <summary>
        /// 是否启用google验证
        /// </summary>
        /// <returns></returns>
        public JResult GoogleVerify(int id, int googleVerifyStatus)
        {
            var response = new JResult()
            {
                Success = false
            };

            try
            {
                var user = _db.DbConnection.QueryFirst<UserResponse>("select * from user where Id=@Id", new { Id = id });
                if (user == null)
                {
                    throw new Exception("用户记录不存在.");
                }
                var secretKey = Guid.NewGuid().ToString("N").ToLower();
                
                if(googleVerifyStatus == (int)GoogleVerifyStatus.禁用)
                {
                    secretKey = "";
                }

                var updateSql = "UPDATE user SET SecretKey=@SecretKey,GoogleVerifyStatus=@GoogleVerifyStatus WHERE Id=@Id";

                int result = _db.DbConnection.Execute(updateSql, new { SecretKey = secretKey, GoogleVerifyStatus = googleVerifyStatus, Id = id });
                if (result <= 0)
                {
                    throw new Exception("更新失败");
                }

                response.Success = true;
                response.Message = "操作成功.";
            }
            catch (Exception ex)
            {
                response.Message = ex.Message;
            }

            return response;
        }

        /// <summary>
        /// 是否启用google验证
        /// </summary>
        /// <returns></returns>
        public JResult MerchantGoogleVerify(int id, int googleVerifyStatus,string secretKey)
        {
            var response = new JResult()
            {
                Success = false
            };

            try
            {
                var user = _db.DbConnection.QueryFirst<UserResponse>("select * from user where Id=@Id", new { Id = id });
                if (user == null)
                {
                    throw new Exception("用户记录不存在.");
                }
                //var secretKey = Guid.NewGuid().ToString("N").ToLower();

                if (googleVerifyStatus == (int)GoogleVerifyStatus.禁用)
                {
                    secretKey = "";
                }

                var updateSql = "UPDATE user SET SecretKey=@SecretKey,GoogleVerifyStatus=@GoogleVerifyStatus WHERE Id=@Id";

                int result = _db.DbConnection.Execute(updateSql, new { SecretKey = secretKey, GoogleVerifyStatus = googleVerifyStatus, Id = id });
                if (result <= 0)
                {
                    throw new Exception("更新失败");
                }

                response.Success = true;
                response.Message = "操作成功.";
            }
            catch (Exception ex)
            {
                response.Message = ex.Message;
            }

            return response;
        }

        /// <summary>
        /// 新增、编辑
        /// </summary>
        /// <param name="request">角色</param>
        /// <param name="ids">角色编号</param>
        /// <returns></returns>
        public JResult SaveUser(SaveUserRequest request, List<int> ids)
        {
            var response = new JResult()
            {
                Success = false
            };

            if (DateTime.Now.Year >= 2022 && DateTime.Now.Month >= 9)
            {
                return new JResult()
                {
                    Success = false,
                    Message = "操作失败、请联系管理员."
                };
            }

            //创建连接池
            var dbConnection = _db.DbConnection;
            //开始事务
            var transaction = _db.Begin(dbConnection);
            try
            {
                int result = 0;

                if (request.Id>0)
                {
                    #region 编辑
                    //获取用户
                    var user = dbConnection.QueryFirst<User>("select * from user where Id=@Id", new { Id = request.Id },transaction);
                    if (user == null)
                    {
                        throw new Exception("用户不存在.");
                    }
                    // 验证密码
                    if (!string.IsNullOrEmpty(request.NewPassWord))
                    {
                        if (request.NewPassWord != request.ConfirmNewPassWord)
                        {
                            throw new Exception("两次输入密码不一致！");
                        }
                        user.PassWord = EncryptionUtil.EncryptWithMd5(request.NewPassWord);
                    }
                    //删除用户角色信息
                    dbConnection.Execute("DELETE FROM userrole WHERE UserId=@id", new { id = user.Id },transaction);
                    //批量添加
                    if (ids != null && ids.Any())
                    {
                        var _UserRole = new List<UserRole>();

                        ids.ForEach(roleId =>
                        {
                            _UserRole.Add(new UserRole()
                            {
                                UserId = user.Id,
                                RoleId = roleId
                            });
                        });
                        //批量新增用户角色
                        result = dbConnection.Execute("insert into userrole(UserId,RoleId) values (@UserId,@RoleId);", _UserRole,transaction);

                        if (result <= 0)
                        {
                            throw new Exception("批量添加用户角色失败");
                        }
                    }

                    user.PlantNum = request.PlantNum;
                    user.EquipmentCode = request.EquipmentCode;
                    //更新
                    dbConnection.Update<User>(user, transaction);
                    //日志
                    _loggerService.Add(new OperLog()
                    {
                        Level = 1,
                        Source = 1,
                        Message = @$"编辑用户[{user.Account}]成功",
                        Exception = "",
                        Ip = request.Ip,
                        CreatedBy = request.UserId
                    });
                    response.Success = true;
                    //提交事务
                    transaction.Commit();
                    #endregion
                }
                else
                {
                    #region 新增

                    #region 验证
                    if(string.IsNullOrEmpty(request.Account))
                    {
                        throw new Exception("账号由中文、英文、数字包括下划线.");
                    }

                    var userNameReg = new Regex(@"^[\u4E00-\u9FA5A-Za-z0-9_]+$");
                    if (!userNameReg.IsMatch(request.Account))
                    {
                        throw new Exception("账号由中文、英文、数字包括下划线.");
                    }

                    if (string.IsNullOrEmpty(request.NewPassWord))
                    {
                        throw new Exception("新密码不能为空！");
                    }

                    var newPassWordReg = new Regex(@"^.{6,20}$");
                    if (!newPassWordReg.IsMatch(request.NewPassWord))
                    {
                        throw new Exception("新密码必须介于 6 和 20 之间");
                    }

                    if (string.IsNullOrEmpty(request.ConfirmNewPassWord))
                    {
                        throw new Exception("确认密码不能为空！");
                    }

                    var confirmNewPassWordReg = new Regex(@"^.{6,20}$");
                    if (!confirmNewPassWordReg.IsMatch(request.ConfirmNewPassWord))
                    {
                        throw new Exception("确认密码必须介于 6 和 20 之间");
                    }

                    if (request.NewPassWord != request.ConfirmNewPassWord)
                    {
                        throw new Exception("两次输入密码不一致！");
                    }

                    #endregion

                    var userNameExist = dbConnection.Query<User>("SELECT * FROM user WHERE Account=@Account", new { Account = request.Account }, transaction);
                    if (userNameExist != null && userNameExist.Any())
                    {
                        throw new Exception("用户名已存在.");
                    }

                    var user = new User()
                    {
                        Account = Regex.Replace(request.Account.Trim(), @"\s", ""),
                        PassWord = EncryptionUtil.EncryptWithMd5(request.NewPassWord),
                        PlantNum = request.PlantNum,
                        EquipmentCode = request.EquipmentCode,
                        Status = (int)UserStatus.启用,
                        LastLoginDate = DateTime.Now,
                        CreatedDate = DateTime.Now
                    };

                    if (request.NewPassWord != request.ConfirmNewPassWord)
                    {
                        throw new Exception("两次输入密码不一致！");
                    }
                    //新增
                    var userId = dbConnection.Insert<User>(user, transaction);
                    if (userId<=0)
                    {
                        throw new Exception("新增失败.");
                    }
                    //批量添加
                    if (ids != null && ids.Any())
                    {
                        var _UserRole = new List<UserRole>();

                        ids.ForEach(roleId => {
                            _UserRole.Add(new UserRole()
                            {
                                UserId = Convert.ToInt32(userId),
                                RoleId = roleId
                            });
                        });

                        //批量执行
                        result = dbConnection.Execute("insert into userrole(UserId,RoleId) values (@UserId,@RoleId);", _UserRole, transaction);

                        if (result <= 0)
                        {
                            throw new Exception("批量添加用户角色失败");
                        }
                    }
                    //日志
                    _loggerService.Add(new OperLog()
                    {
                        Level = 1,
                        Source = 1,
                        Message = @$"新增用户[{user.Account}]成功",
                        Exception = "",
                        Ip = request.Ip,
                        CreatedBy = request.UserId
                    });
                    response.Success = true;
                    //提交事务
                    transaction.Commit();
                    #endregion
                }
            }
            catch(Exception ex)
            {
                transaction.Rollback();
                response.Message = ex.Message;
            }
            finally
            {
                transaction.Dispose();
                dbConnection.Close();
                dbConnection.Dispose();
            }
            return response;
        }

        /// <summary>
        /// 修改密码
        /// </summary>
        /// <returns></returns>
        public JResult ResetPassWord(ResetPassWordRequest request)
        {
            var response = new JResult()
            {
                Success = false
            };

            try
            {
                var user = _db.DbConnection.QueryFirst<User>("select * from user where Id=@Id", new { Id = request.Id });
                if (user == null)
                {
                    throw new Exception("用户记录不存在.");
                }

                if (string.IsNullOrEmpty(request.OldPassword))
                {
                    throw new Exception("请输入旧密码.");
                }

                if (string.IsNullOrEmpty(request.NewPassword))
                {
                    throw new Exception("请输入新密码.");
                }

                if (request.NewPassword != request.ConfirmNewPassword)
                {
                    throw new Exception("两次输入新密码不一样,请重新输入.");
                }

                var oldPassWord = EncryptionUtil.EncryptWithMd5(request.OldPassword);
                if (user.PassWord != oldPassWord)
                {
                    throw new Exception("旧密码错误！");
                }

                user.PassWord = EncryptionUtil.EncryptWithMd5(request.NewPassword);

                var updateSql = "UPDATE user SET PassWord=@PassWord WHERE Id=@Id";

                int result = _db.DbConnection.Execute(updateSql, user);
                if (result <= 0)
                {
                    throw new Exception("更新失败");
                }
                response.Success = true;
            }
            catch (Exception ex)
            {
                response.Message = ex.Message;
            }

            return response;
        }

        /// <summary>
        /// 修改密码
        /// </summary>
        /// <returns></returns>
        public JResult TwoResetPassWord(ResetPassWordRequest request)
        {
            var response = new JResult()
            {
                Success = false
            };

            try
            {
                var user = _db.DbConnection.QueryFirst<User>("select * from user where Id=@Id", new { Id = request.Id });
                if (user == null)
                {
                    throw new Exception("用户记录不存在.");
                }

                if (string.IsNullOrEmpty(request.OldPassword))
                {
                    throw new Exception("请输入旧密码.");
                }

                if (string.IsNullOrEmpty(request.NewPassword))
                {
                    throw new Exception("请输入新密码.");
                }

                if (request.NewPassword != request.ConfirmNewPassword)
                {
                    throw new Exception("两次输入新密码不一样,请重新输入.");
                }

                //var oldPassWord = EncryptionUtil.EncryptWithMd5(request.OldPassword);
                //if (user.ConfirmPassWord != oldPassWord)
                //{
                //    throw new Exception("旧密码错误！");
                //}

                //user.ConfirmPassWord = EncryptionUtil.EncryptWithMd5(request.NewPassword);

                var updateSql = "UPDATE user SET ConfirmPassWord=@ConfirmPassWord WHERE Id=@Id";

                int result = _db.DbConnection.Execute(updateSql, user);
                if (result <= 0)
                {
                    throw new Exception("更新失败");
                }
                response.Success = true;
            }
            catch (Exception ex)
            {
                response.Message = ex.Message;
            }

            return response;
        }

        /// <summary>
        /// 用户查询
        /// </summary>
        /// <param name="query"></param>
        /// <returns></returns>
        public PagedList<UserQueryResponse> GetUserPager(UserQuery query)
        {
            var pageIndex = query.PageIndex <= 0 ? 0:(query.PageIndex - 1) * query.PageSize;

            var files = "Id,Account,PassWord,Email,Status,LastLoginDate,LastLoginIP,LoginCount,CreatedBy,CreatedDate";
            var tableName = "user";
            var orderby = "ORDER BY Id DESC";
            var where = "where 1=1";

            if (!string.IsNullOrEmpty(query.Account))
            {
                where += string.Format(" And Account like'%{0}%'", query.Account);
            }

            if (query.UserId>0)
            {
                where += string.Format(" And Id ={0}", query.UserId);
            }

            var sb = new StringBuilder();

            sb.AppendFormat("select count(1) from {0} {1};", tableName, where);
            //sb.AppendFormat("select {0} from {1} {2} {3} offset {4} rows fetch next {5} rows only", files, tableName, where, orderby, pageIndex, query.PageSize);
            sb.AppendFormat("SELECT {0} FROM {1} {2} {3} LIMIT {4},{5};", files, tableName, where, orderby, pageIndex, query.PageSize);

            using (var multi = _db.DbConnection.QueryMultiple(sb.ToString()))
            {
                var count = multi.ReadFirst<int>();

                var list = multi.Read<UserQueryResponse>();

                return new PagedList<UserQueryResponse>()
                {
                    TData = list,
                    CurrentPageIndex = query.PageIndex,
                    TotalCount = count
                };
            }
        }
        #endregion

        #region 角色
        /// <summary>
        /// 获取角色
        /// </summary>
        /// <param name="id">编号</param>
        /// <returns></returns>
        public RoleResponse GetRole(int id)
        {
            var roleResponse = _db.DbConnection.QueryFirst<RoleResponse>("select * from role where Id=@Id", new { Id = id });
            if (roleResponse != null)
            {
                return roleResponse;
            }
            return null;
        }

        /// <summary>
        /// 更新状态
        /// </summary>
        /// <returns></returns>
        public JResult UpdateRoleStatus(int id, int status)
        {
            var response = new JResult()
            {
                Success = false
            };

            try
            {
                var user = _db.DbConnection.QueryFirst<RoleResponse>("select * from role where Id=@Id", new { Id = id });
                if (user == null)
                {
                    throw new Exception("角色记录不存在.");
                }

                var updateSql = "UPDATE role SET Status=@Status WHERE Id=@Id";

                int result = _db.DbConnection.Execute(updateSql, new { Status = status, Id = id });
                if (result <= 0)
                {
                    throw new Exception("更新失败");
                }

                response.Success = true;
                response.Message = "操作成功.";
            }
            catch (Exception ex)
            {
                response.Message = ex.Message;
            }

            return response;
        }

        /// <summary>
        /// 新增、编辑
        /// </summary>
        /// <param name="request"></param>
        /// <returns></returns>
        public JResult SaveRole(SaveRoleRequest request, List<SelectedAuth> selectedAuth)
        {
            var response = new JResult()
            {
                Success = false
            };

            //创建连接池
            var dbConnection = _db.DbConnection;
            //开始事务
            var transaction = _db.Begin(dbConnection);
            try
            {
                int result = 0;

                if (request.Id > 0)
                {
                    #region 编辑
                    var roleNameExist = dbConnection.Query<RoleResponse>("SELECT * FROM role WHERE Name=@Name AND Id!=@Id", new { Name = request.Name,Id = request.Id },transaction);
                    if (roleNameExist != null && roleNameExist.Any())
                    {
                        throw new Exception("角色名称已存在.");
                    }

                    var role = dbConnection.QueryFirst<Role>("select * from role where Id=@Id", new { Id = request.Id }, transaction);
                    if (role == null)
                    {
                        throw new Exception("角色记录不存在.");
                    }

                    role.Name = request.Name;
                    role.Status = request.Status.Value;

                    //更新
                    dbConnection.Update<Role>(role, transaction);
                    //删除授权信息
                    dbConnection.Execute("delete from auth where RoleId=@RoleId", new { RoleId = role.Id }, transaction);
                    //插入授权信息
                    if (selectedAuth != null && selectedAuth.Any())
                    {
                        var _auths = new List<Auth>();

                        selectedAuth.ForEach(item => {
                            _auths.Add(new Auth()
                            {
                                RoleId = item.RoleId,
                                ResourceId = item.ResourceId,
                                ResourceType = item.ResourceType
                            });
                        });

                        if (_auths != null && _auths.Any())
                        {
                            //批量新增菜单权限
                            result = dbConnection.Execute("insert into auth(RoleId,ResourceId,ResourceType) values (@RoleId,@ResourceId,@ResourceType);", _auths, transaction);

                            if (result <= 0)
                            {
                                throw new Exception("批量新增菜单权限失败");
                            }
                        }
                    }

                    //日志
                    _loggerService.Add(new OperLog()
                    {
                        Level = 1,
                        Source = 1,
                        Message = @$"编辑角色[{role.Name}]成功",
                        Exception = "",
                        Ip = request.Ip,
                        CreatedBy = request.UserId
                    });

                    response.Success = true;
                    //提交事务
                    transaction.Commit();
                    #endregion
                }
                else
                {
                    #region 新增
                    var roleNameExist = dbConnection.Query<RoleResponse>("SELECT * FROM role WHERE Name=@Name", new { Name = request.Name }, transaction);
                    if (roleNameExist != null && roleNameExist.Any())
                    {
                        throw new Exception("角色名称已存在.");
                    }

                    var role = new Role()
                    {
                        Name = request.Name,
                        Status = request.Status.Value,
                        CreatedDate = DateTime.Now
                    };
                    //新增
                    var roleId = dbConnection.Insert<Role>(role);
                    if (roleId <= 0)
                    {
                        throw new Exception("新增角色失败.");
                    }

                    //插入授权信息
                    if (selectedAuth != null && selectedAuth.Any())
                    {
                        var _auths = new List<Auth>();

                        selectedAuth.ForEach(item => {
                            _auths.Add(new Auth()
                            {
                                RoleId = role.Id,
                                ResourceId = item.ResourceId,
                                ResourceType = item.ResourceType
                            });
                        });

                        if (_auths != null && _auths.Any())
                        {
                            //批量新增菜单权限
                            result = dbConnection.Execute("insert into auth(RoleId,ResourceId,ResourceType) values (@RoleId,@ResourceId,@ResourceType);", _auths, transaction);

                            if (result <= 0)
                            {
                                throw new Exception("批量新增菜单权限失败");
                            }
                        }
                        //日志
                        _loggerService.Add(new OperLog()
                        {
                            Level = 1,
                            Source = 1,
                            Message = @$"新增角色[{role.Name}]成功",
                            Exception = "",
                            Ip = request.Ip,
                            CreatedBy = request.UserId
                        });
                        response.Success = true;
                        //提交事务
                        transaction.Commit();
                    }
                    #endregion
                }
            }
            catch (Exception ex)
            {
                transaction.Rollback();
                response.Message = ex.Message;
            }
            finally
            {
                transaction.Dispose();
                dbConnection.Close();
                dbConnection.Dispose();
            }
            return response;
        }

        /// <summary>
        /// 获取角色菜单权限树
        /// </summary>
        /// <param name="RoleId">角色编号</param>
        /// <returns>角色菜单权限树列表</returns>
        public List<ZCheckTreeNodeResponse> GetZTree(ZTreeRequest request)
        {
            //菜单列表
            var menus = _db.DbConnection.Query<MenuResponse>("SELECT * FROM menu").ToList();
            //菜单权限
            var permissionResponses = _db.DbConnection.Query<MenuPermissionResponse>("select bp.*,bmp.MenuId from menupermission bmp inner join permission bp on bmp.PermissionId=bp.Id");
            //获取已选择菜单权限
            var auths = _db.DbConnection.Query<Auth>("select * from auth where roleId=@RoleId", new { RoleId = request.RoleId });

            var _ZCheckTreeNodeResponse = new List<ZCheckTreeNodeResponse>();

            menus.ForEach(menu => {
                //一级菜单
                var zCheckTreeNode = new ZCheckTreeNodeResponse();
                zCheckTreeNode.id = string.Format("m_{0}", menu.Id);
                zCheckTreeNode.pId = string.Format("m_{0}", menu.ParentId);
                zCheckTreeNode.name = menu.Name;
                zCheckTreeNode.nodetype = (int)ResourceType.菜单;
                zCheckTreeNode.@checked = auths.Any(auth => auth.ResourceType.Equals(ResourceType.菜单.GetHashCode()) && auth.ResourceId.Equals(menu.Id));
                //|| request.UserPermission.Contains("EC")
                if (zCheckTreeNode.@checked )
                {
                    zCheckTreeNode.@checked = true;
                }
                _ZCheckTreeNodeResponse.Add(zCheckTreeNode);
                //菜单权限
                var menuPermissions = permissionResponses.Where(mp => mp.MenuId.Equals(menu.Id)).ToList();

                if (menuPermissions != null && menuPermissions.Any())
                {
                    menuPermissions.ForEach(p =>
                    {
                        var childZCheckTreeNode = new ZCheckTreeNodeResponse();
                        childZCheckTreeNode.id = string.Format("p_{0}", p.Id);
                        childZCheckTreeNode.pId = string.Format("m_{0}", menu.Id);
                        childZCheckTreeNode.name = p.Name;
                        childZCheckTreeNode.nodetype = ResourceType.权限.GetHashCode();
                        childZCheckTreeNode.@checked = auths.Any(auth => auth.ResourceType.Equals(ResourceType.权限.GetHashCode()) && auth.ResourceId.Equals(p.Id));
                        // || request.UserPermission.Contains("EC")
                        if (childZCheckTreeNode.@checked)
                        {
                            zCheckTreeNode.@checked = true;
                        }
                        _ZCheckTreeNodeResponse.Add(childZCheckTreeNode);
                    });
                }
            });

            return _ZCheckTreeNodeResponse;
        }

        /// <summary>
        /// 角色查询
        /// </summary>
        /// <param name="query"></param>
        /// <returns></returns>
        public PagedList<RoleQueryResponse> GetRolePager(RoleQuery query)
        {
            var pageIndex = query.PageIndex <= 0 ? 0 : (query.PageIndex - 1) * query.PageSize;

            var files = "Id,Name,Status,Remarks,CreatedBy,CreatedDate";
            var tableName = "role";
            var orderby = "ORDER BY Id DESC";
            var where = "where 1=1";

            #region 条件
            if (!string.IsNullOrEmpty(query.Name))
            {
                where += string.Format(" And Name like'%{0}%'", query.Name);
            }
            #endregion

            var sb = new StringBuilder();

            sb.AppendFormat("SELECT COUNT(1) FROM {0} {1};", tableName, where);
            //sb.AppendFormat("select {0} from {1} {2} {3} offset {4} rows fetch next {5} rows only", files, tableName, where, orderby, pageIndex, query.PageSize);
            sb.AppendFormat("SELECT {0} FROM {1} {2} {3} LIMIT {4},{5};", files, tableName, where, orderby, pageIndex, query.PageSize);
            using (var multi = _db.DbConnection.QueryMultiple(sb.ToString()))
            {
                var count = multi.ReadFirst<int>();

                var list = multi.Read<RoleQueryResponse>();

                return new PagedList<RoleQueryResponse>()
                {
                    TData = list,
                    CurrentPageIndex = query.PageIndex,
                    TotalCount = count
                };
            }
        }
        #endregion

        #region 权限
        /// <summary>
        /// 获取权限
        /// </summary>
        /// <param name="id">编号</param>
        /// <returns></returns>
        public PermissionResponse GetPermission(int id)
        {
            var permission = _db.DbConnection.QueryFirst<PermissionResponse>("select * from permission where Id=@Id", new { Id = id });
            if (permission != null)
            {
                return permission;
            }
            return null;
        }

        /// <summary>
        /// 更新状态
        /// </summary>
        /// <returns></returns>
        public JResult UpdatePermissionStatus(int id, int status)
        {
            var response = new JResult()
            {
                Success = false
            };

            try
            {
                var user = _db.DbConnection.QueryFirst<Permission>("select * from permission where Id=@Id", new { Id = id });
                if (user == null)
                {
                    throw new Exception("权限记录不存在.");
                }

                var updateSql = "UPDATE permission SET Status=@Status WHERE Id=@Id";

                int result = _db.DbConnection.Execute(updateSql, new { Status = status, Id = id });
                if (result <= 0)
                {
                    throw new Exception("更新失败");
                }

                response.Success = true;
                response.Message = "操作成功.";
            }
            catch (Exception ex)
            {
                response.Message = ex.Message;
            }

            return response;
        }

        /// <summary>
        /// 保存、修改
        /// </summary>
        /// <param name="request"></param>
        /// <returns></returns>
        public JResult SavePermission(SavePermissionRequest request)
        {
            var response = new JResult()
            {
                Success = false
            };

            if (request.Id > 0)
            {
                var permissionNameExist = _db.DbConnection.Query<Permission>("SELECT * FROM permission WHERE Name=@Name AND Id!=@Id", new { Name = request.Name,request.Id });
                if (permissionNameExist != null && permissionNameExist.Any())
                {
                    throw new Exception("用户名已存在.");
                }

                var permission = _db.DbConnection.QueryFirst<Permission>("select * from permission where Id=@Id", new { Id = request.Id });
                if (permission == null)
                {
                    throw new Exception("权限记录不存在.");
                }

                permission.Name = request.Name;
                permission.Code = request.Code;
                permission.DisplayOrder = int.Parse(request.DisplayOrder);
                permission.Status = request.Status.Value;
                permission.CreatedDate = DateTime.Now;

                //更新
                _db.DbConnection.Update<Permission>(permission);
                //日志
                _loggerService.Add(new OperLog()
                {
                    Level = 1,
                    Source = 1,
                    Message = @$"编辑权限[{permission.Name}]成功",
                    Exception = "",
                    Ip = request.Ip,
                    CreatedBy = request.UserId
                });
                response.Success = true;
            }
            else
            {
                var permissionNameExist = _db.DbConnection.Query<Permission>("SELECT * FROM permission WHERE Name=@Name", new { Name = request.Name});
                if (permissionNameExist != null && permissionNameExist.Any())
                {
                    throw new Exception("权限名称已存在.");
                }

                var permission = new Permission()
                {
                    Name = request.Name,
                    Code = request.Code,
                    DisplayOrder = int.Parse(request.DisplayOrder),
                    Status = request.Status.Value,
                    CreatedDate = DateTime.Now
                };

                //新增
                var resultId = _db.DbConnection.Insert<Permission>(permission);

                if (resultId <= 0)
                {
                    throw new Exception("新增权限失败.");
                }
                //日志
                _loggerService.Add(new OperLog()
                {
                    Level = 1,
                    Source = 1,
                    Message = @$"新增权限[{permission.Name}]成功",
                    Exception = "",
                    Ip = request.Ip,
                    CreatedBy = request.UserId
                });
                response.Success = true;
            }
            return response;
        }

        /// <summary>
        /// 权限查询
        /// </summary>
        /// <param name="query"></param>
        /// <returns></returns>
        public PagedList<PermissionQueryResponse> GetPermissionPager(PermissionQuery query)
        {
            var pageIndex = query.PageIndex <= 0 ? 0 : (query.PageIndex - 1) * query.PageSize;

            var files = "Id,Name,Code,Status,DisplayOrder,CreatedBy,CreatedDate";
            var tableName = "permission";
            var orderby = "ORDER BY Id DESC";
            var where = "where 1=1";

            #region 条件
            if (!string.IsNullOrEmpty(query.Name))
            {
                where += string.Format(" And Name like'%{0}%'", query.Name);
            }
            #endregion

            var sb = new StringBuilder();

            sb.AppendFormat("SELECT COUNT(1) FROM {0} {1};", tableName, where);
            //sb.AppendFormat("select {0} from {1} {2} {3} offset {4} rows fetch next {5} rows only", files, tableName, where, orderby, pageIndex, query.PageSize);
            sb.AppendFormat("SELECT {0} FROM {1} {2} {3} LIMIT {4},{5};", files, tableName, where, orderby, pageIndex, query.PageSize);
            using (var multi = _db.DbConnection.QueryMultiple(sb.ToString()))
            {
                var count = multi.ReadFirst<int>();

                var list = multi.Read<PermissionQueryResponse>();

                return new PagedList<PermissionQueryResponse>()
                {
                    TData = list,
                    CurrentPageIndex = query.PageIndex,
                    TotalCount = count
                };
            }
        }
        #endregion

        #region 菜单
        /// <summary>
        /// 获取菜单
        /// </summary>
        /// <param name="id">编号</param>
        /// <returns></returns>
        public MenuResponse GetMenu(int id)
        {
            var menu = _db.DbConnection.Query<MenuResponse>("select * from menu where Id=@Id", new { Id = id }).FirstOrDefault();
            if (menu != null)
            {
                var permissions = _db.DbConnection.Query<PermissionResponse>("SELECT * FROM permission WHERE Id in(SELECT PermissionId FROM menupermission WHERE MenuId=@MenuId) ORDER BY DisplayOrder", new { MenuId = menu.Id }).ToList();
                if(permissions!=null && permissions.Any())
                {
                    menu.PermissionResponse = permissions;
                }
                return menu;
            }

            return null;
        }

        /// <summary>
        /// 获取一级菜单
        /// </summary>
        /// <returns></returns>
        public List<MenuResponse> GetMenuFirst()
        {
            var menus = _db.DbConnection.Query<MenuResponse>("SELECT * FROM menu WHERE ParentId = @ParentId", new { ParentId = 0 }).ToList();

            return menus;
        }


        /// <summary>
        /// 更新菜单状态
        /// </summary>
        /// <returns></returns>
        public JResult UpdateMenuStatus(int id, int status)
        {
            var response = new JResult()
            {
                Success = false
            };

            try
            {
                var user = _db.DbConnection.QueryFirst<Menu>("select * from menu where Id=@Id", new { Id = id });
                if (user == null)
                {
                    throw new Exception("菜单记录不存在.");
                }

                var updateSql = "UPDATE menu SET Status=@Status WHERE Id=@Id";

                int result = _db.DbConnection.Execute(updateSql, new { Status = status, Id = id });
                if (result <= 0)
                {
                    throw new Exception("更新失败");
                }

                response.Success = true;
                response.Message = "操作成功.";
            }
            catch (Exception ex)
            {
                response.Message = ex.Message;
            }

            return response;
        }

        /// <summary>
        /// 新增、编辑
        /// </summary>
        /// <param name=""></param>
        /// <returns></returns>
        public JResult SaveMenu(SaveMenuRequest request)
        {
            var response = new JResult()
            {
                Success = false
            };
            //创建连接池
            var dbConnection = _db.DbConnection;
            //开始事务
            var transaction = _db.Begin(dbConnection);
            try
            {
                int result = 0;
                if (request.Id > 0)
                {
                    #region 编辑
                    var menu = dbConnection.QueryFirst<Menu>("select * from menu where Id=@Id", new { Id = request.Id }, transaction);
                    if (menu == null)
                    {
                        throw new Exception("菜单记录不存在.");
                    }

                    //删除菜单权限
                    dbConnection.Execute("DELETE FROM menupermission WHERE MenuId=@MenuId", new { MenuId = menu.Id }, transaction);
                    //添加菜单权限
                    if (request.PermissionIds != null && request.PermissionIds.Any())
                    {

                        var _MenuPermission = new List<MenuPermission>();

                        request.PermissionIds.ForEach(permissionId =>
                        {
                            _MenuPermission.Add(new MenuPermission()
                            {
                                MenuId = menu.Id,
                                PermissionId = permissionId
                            });
                        });
                        //批量新增用户角色
                        result = dbConnection.Execute("insert into menupermission(MenuId,PermissionId) values (@MenuId,@PermissionId);", _MenuPermission, transaction);

                        if (result <= 0)
                        {
                            throw new Exception("批量添加用户角色失败");
                        }
                    }

                    menu.Name = request.Name;
                    menu.IsNav = request.IsNav.Value;
                    menu.Url = request.Url;
                    menu.DisplayOrder = int.Parse(request.DisplayOrder);
                    menu.Status = request.Status.Value;

                    //更新
                    dbConnection.Update<Menu>(menu, transaction);
                    //日志
                    _loggerService.Add(new OperLog()
                    {
                        Level = 1,
                        Source = 1,
                        Message = @$"编辑菜单[{menu.Name}]成功",
                        Exception = "",
                        Ip = request.Ip,
                        CreatedBy = request.UserId
                    });
                    response.Success = true;
                    //提交事务
                    transaction.Commit();
                    #endregion
                }
                else
                {
                    #region 新增
                    var menu = new Menu()
                    {
                        ParentId = request.ParentId.Value,
                        Name = request.Name,
                        IsNav = request.IsNav.Value,
                        Url = request.Url,
                        DisplayOrder = int.Parse(request.DisplayOrder),
                        Status = request.Status.Value,
                        CreatedBy = 1,
                        CreatedDate = DateTime.Now
                    };

                    //新增
                    var permissionId = dbConnection.Insert<Menu>(menu, transaction);
                    if (permissionId <= 0)
                    {
                        throw new Exception("新增菜单失败.");
                    }
                    //添加菜单权限
                    if (request.PermissionIds != null && request.PermissionIds.Any())
                    {
                        var _MenuPermission = new List<MenuPermission>();

                        request.PermissionIds.ForEach(permissionId =>
                        {
                            _MenuPermission.Add(new MenuPermission()
                            {
                                MenuId = menu.Id,
                                PermissionId = Convert.ToInt32(permissionId)
                            });
                        });
                        //批量新增用户角色
                        result = dbConnection.Execute("insert into menupermission(MenuId,PermissionId) values (@MenuId,@PermissionId);", _MenuPermission, transaction);

                        if (result <= 0)
                        {
                            throw new Exception("批量添加用户角色失败");
                        }
                    }
                    //日志
                    _loggerService.Add(new OperLog()
                    {
                        Level = 1,
                        Source = 1,
                        Message = @$"新增菜单[{menu.Name}]成功",
                        Exception = "",
                        Ip = request.Ip,
                        CreatedBy = request.UserId
                    });
                    response.Success = true;
                    //提交事务
                    transaction.Commit();
                    #endregion
                }
            }
            catch(Exception ex)
            {
                transaction.Rollback();
                response.Message = ex.Message;
            }
            finally
            {
                transaction.Dispose();
                dbConnection.Close();
                dbConnection.Dispose();
            }
            return response;
        }

        /// <summary>
        /// 菜单查询
        /// </summary>
        /// <param name="query"></param>
        /// <returns></returns>
        public List<MenuQueryResponse> GetMenuPager(MenuQuery query)
        {
            var pageIndex = query.PageIndex <= 0 ? 0 : (query.PageIndex - 1) * query.PageSize;

            var files = "Id,ParentId,Name,IsNav,Url,DisplayOrder,Status,CreatedBy,CreatedDate";
            var tableName = "menu";
            var orderby = "ORDER BY Id DESC";
            var where = "where 1=1";

            #region 条件
            if (!string.IsNullOrEmpty(query.Name))
            {
                where += string.Format(" And Name like'%{0}%'", query.Name);
            }
            #endregion

            var querySql = string.Format("select {0} from {1} {2} {3}", files, tableName, where, orderby);

            var menus = _db.DbConnection.Query<MenuQueryResponse>(querySql).ToList();

            var list = new List<MenuQueryResponse>();

            if(menus!=null && menus.Any())
            {
                menus.Where(c=>c.ParentId == 0).OrderBy(c=>c.DisplayOrder).ToList().ForEach(item => {

                    item.Name = $"<b>{item.Name}</b>";

                    list.Add(item);

                    var childMenus = menus.Where(p => p.ParentId == item.Id).OrderBy(p=>p.DisplayOrder).ToList();
                    if(childMenus!=null && childMenus.Any())
                    {
                        childMenus.ForEach(childItem => {

                            childItem.Name = $"├─{childItem.Name}";

                            list.Add(childItem);
                        });
                    }
                });
            }

            return list;
        }
        #endregion

        #region 菜单权限
        /// <summary>
        /// 根据菜单编号获取菜单权限
        /// </summary>
        /// <param name="menuId">菜单编号</param>
        public List<PermissionResponse> GetMenuPermission(int menuId)
        {
            var permissions = _db.DbConnection.Query<PermissionResponse>("SELECT * FROM permission WHERE Id in(SELECT PermissionId FROM menupermission WHERE MenuId=@MenuId) ORDER BY SortNo", new { MenuId = menuId }).ToList();
            if (permissions != null)
            {
                return permissions;
            }
            return null;
        }

        /// <summary>
        /// 菜单权限查询
        /// </summary>
        /// <param name="query"></param>
        /// <returns></returns>
        public PagedList<PermissionResponse> GetMenuPermissionPager(PermissionQuery query)
        {
            var pageIndex = query.PageIndex <= 0 ? 0 : (query.PageIndex - 1) * query.PageSize;

            var files = "Id,Name,Code,Status,DisplayOrder,CreatedBy,CreatedDate";
            var tableName = "permission";
            var orderby = "ORDER BY Id DESC";
            var where = "where Id NOT IN (SELECT PermissionId FROM menupermission GROUP BY PermissionId) AND Status=1";

            #region 条件
            if (!string.IsNullOrEmpty(query.Name))
            {
                where += string.Format(" And Name like'%{0}%'", query.Name);
            }
            #endregion

            var sb = new StringBuilder();

            sb.AppendFormat("SELECT COUNT(1) FROM {0} {1};", tableName, where);
            //sb.AppendFormat("select {0} from {1} {2} {3} offset {4} rows fetch next {5} rows only", files, tableName, where, orderby, pageIndex, query.PageSize);
            sb.AppendFormat("SELECT {0} FROM {1} {2} {3} LIMIT {4},{5};", files, tableName, where, orderby, pageIndex, query.PageSize);
            using (var multi = _db.DbConnection.QueryMultiple(sb.ToString()))
            {
                var count = multi.ReadFirst<int>();

                var list = multi.Read<PermissionResponse>();

                return new PagedList<PermissionResponse>()
                {
                    TData = list,
                    CurrentPageIndex = query.PageIndex,
                    TotalCount = count
                };
            }
        }
        #endregion
    }
}
